package com.it2297.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.it2297.main.App;
import com.it2297.main.CompanyApp;

public class Company {

	private int companyId;
	private String companyName;
	private String email;
	private int phone;
	private String address;
	private	String description;
	private String password;
	private int numOfJobs;
	private String recoveryQuestion;
	private String recoveryAnswer;
	
	public String getRecoveryQuestion() {
		return recoveryQuestion;
	}
	public void setRecoveryQuestion(String recoveryQuestion) {
		this.recoveryQuestion = recoveryQuestion;
	}
	public String getRecoveryAnswer() {
		return recoveryAnswer;
	}
	public void setRecoveryAnswer(String recoveryAnswer) {
		this.recoveryAnswer = recoveryAnswer;
	}
	public int getNumOfJobs() {
		return numOfJobs;
	}
	public void setNumOfJobs(int numOfJobs) {
		this.numOfJobs = numOfJobs;
	}
	public int getCompanyId() {
		return companyId;
	}
	public void setCompanyId(int companyId) {
		this.companyId = companyId;
	}
	public String getCompanyName() {
		return companyName;
	}
	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public int getPhone() {
		return phone;
	}
	public void setPhone(int phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
	public static boolean createCompany(Company company){
		boolean success = false;
		
		String name=company.getCompanyName().toString();
		String password = company.getPassword().toString();
		String email=company.getEmail().toString();
		int phone = company.getPhone();
		String address=company.getAddress().toString();
		String description=company.getDescription().toString();
		String rqn=company.getRecoveryQuestion().toString();
		String rans=company.getRecoveryAnswer().toString();
		
		try{
			App.mySqlConnection.open();
			
			String sql = "INSERT INTO americano.company(name,password,email,phone,address,description,rquestion,ranswer)";
			sql+="VALUES ('" + name + "','" + password + "','" + email + "','" + phone + "','" + address + "','" + description + "', '" + rqn + "','" + rans + "')";
			App.mySqlConnection.executeUpdate(sql);
				success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
			
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	public static boolean IsCompanyExist(String companyName){
		boolean result = false;
		ResultSet rs = null;
		String sql = "SELECT * FROM americano.company WHERE name='" + companyName + "'";
		
		try {
			App.mySqlConnection.open();
			rs = App.mySqlConnection.executeQuery(sql);
			if (rs.next())
				result = true;
			else
				result = false;
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return result;
	}
	
	public static boolean updateDetails(Company company) {
		boolean success = false;

		String name=company.getCompanyName().toString();
		String password=company.getPassword().toString();
		String email=company.getEmail().toString();
		int phone = company.getPhone();
		String address=company.getAddress().toString();
		String description=company.getDescription().toString();
		String rqn=company.getRecoveryQuestion().toString();
		String rans=company.getRecoveryAnswer().toString();
		
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.company SET password = '" + password + "', email = '" + email + "' , phone = '" + phone + "', address = '" + address + "', description = '" + description + "', rquestion= '" + rqn + "', ranswer='" + rans + "'";
			sql += "WHERE name='" + name + "'";
			
			App.mySqlConnection.executeUpdate(sql);
			success = true;
			
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return success;
	}
	
	public static Company retrieveDetails(String companyName) {
		Company company = new Company();
		ResultSet rs = null;

		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.company WHERE name='";
			sql += companyName + "'";
			rs = App.mySqlConnection.executeQuery(sql);

			if (rs.next()) {
				company.setCompanyId(rs.getInt("company_id"));
				company.setCompanyName(rs.getString("name"));
				company.setPassword(rs.getString("password"));
				company.setEmail(rs.getString("email"));
				company.setPhone(rs.getInt("phone"));
				company.setAddress(rs.getString("address"));
				company.setDescription(rs.getString("description"));
				company.setRecoveryQuestion(rs.getString("rquestion"));
				company.setRecoveryAnswer(rs.getString("ranswer"));		
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		finally {
			App.mySqlConnection.close();
		}
		return company;
	}
	
	public ArrayList<String> retrieveCompanyName(){
		ArrayList<String> arr_CompanyName = new ArrayList<String>();
		ResultSet rs = null;
		try {
			App.mySqlConnection.open();
			String sql = "SELECT name FROM americano.company";
			rs=App.mySqlConnection.executeQuery(sql);
			
			while(rs.next()){
				arr_CompanyName.add(rs.getString("name"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			App.mySqlConnection.close();
		}
		return arr_CompanyName;
	}	
	
	

}
